#!/usr/bin/env python
# -*- coding: utf-8 -*-

import os.path
import re
import tornado.database
import tornado.httpserver
import tornado.ioloop
import tornado.options
import tornado.web
import base64
import hashlib
import calendar

from datetime import datetime
from  basehandler import *

class ShowHandler(BaseHandler):
    def get(self,action,tid):
        print action,tid

class OrderHandler(BaseHandler):
    def get(self,args):
        v = self.best(args)
        categories = self.db.query("select * from categories")
        self.render("index.html",categories=categories,items=v)

    def best(self,kw):
        today = datetime.utcnow()
        month_begin,month_end = calendar.monthrange(today.year,today.month)

        # 各种排序条件
        if kw=="order/today":
            where = " and r.datetime>'"+str(datetime(today.year,today.month,today.day-1))+"' and r.datetime<'"+str(datetime(today.year,today.month,today.day))+"' "
        elif kw=="order/month":
            where = " and r.datetime>'"+str(datetime(today.year,today.month,1))+"' and r.datetime<'"+str(datetime(today.year,today.month,month_end))+"' "
        # 默认按年排序
        else:
            where = " and r.datetime>'"+str(datetime(today.year, 1, 1))+"' and r.datetime<'"+str(datetime(today.year+1, 1, 1))+"' "
            
        # sql组装
        sql = "select i.*,sum(r.rank) as rank,a.ename from items as i left join rating as r on i.id=r.iid left join attachments_rel as ar on i.id=ar.iid and ar.type=0 left join attachments as a on ar.aid=a.id where i.delete=0"+where+" group by r.iid order by r.datetime DESC"

        res = self.db.query(sql)
        return res

